package SpdgBI.PhErp;

import SpdgBI.Common.DBUtils;
import SpdgBI.Common.Udfs;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import static com.mchange.v2.c3p0.dbms.OracleUtils.createTemporaryCLOB;

public class Inventory {
    public void PumpData(String PhName){
        String[] strDataType = {"NUMBER", "NVARCHAR2", "VARCHAR", "NCHAR", "CHAR", "CLOB"};
        List<String> lstDataType = new ArrayList<>(Arrays.asList(strDataType));
        List lstTblInfo = null;
        Connection connBI = null;
        Connection connPUBERP = null;
        PreparedStatement pstmtDp = null;
        PreparedStatement pstmtDataSrc = null;
        ResultSet rsDataSrc = null;
        String strSqlDataSrc = "DECLARE @EndDate DATETIME;\n" +
                "SET @EndDate = CONVERT(DATETIME,?,121);\n" +
                "SELECT P.ProjectId,\n" +
                "  P.ProjectDisplayCode,\n" +
                "  P.ProjectTitle,\n" +
                "  ISNULL(P.BookPrice, 0) BookPrice,\n" +
                "  ISNULL(SP.PrintSheetAmount, 0) AS SheetCount,\n" +
                "  ISNULL(P.PrintingAmount, 0) AS PrintingAmount,\n" +
                "  ISNULL(ROUND(SP.PrintSheetAmount * P.PrintingAmount, 3), 0) AS SheetCountSUM,\n" +
                "  ISNULL(ROUND(P.BOOKPRICE * P.PrintingAmount, 2), 0) AS SumListInCome,\n" +
                "  ISNULL(POF.CheckedFeeTotal, 0) - ISNULL(CF.COSTSUM, 0) CheckedFeeTotal,\n" +
                "  ISNULL(CA.AverageUnitPrice, 0) AverageUnitPrice_6,\n" +
                "  ISNULL(CA.CFPrintAmount, 0) - ISNULL(PCI.CheckInAmount, 0) CFPrintAmount,\n" +
                "  ISNULL(CA.CFPrintCost, 0) - ISNULL(PCI.CheckInCost, 0) CFPrintCost,\n" +
                "  (ISNULL(P.PrintingAmount, 0) - ISNULL(CA.CFPrintAmount, 0) +\n" +
                "   ISNULL(PCI.CheckInAmount, 0)) - ISNULL(cacba.CaudaBookAmount, 0) AS WaitCFPrintAmountCost,\n" +
                "  (ISNULL(POF.CheckedFeeTotal, 0) - ISNULL(CA.CFPrintCost, 0) -\n" +
                "   ISNULL(CA.AdjustCost, 0) - ISNULL(CF.COSTSUM, 0) +\n" +
                "   ISNULL(PCI.CheckInCost, 0) + ISNULL(AMI.PrintCostMargin, 0)) AS WaitCFCheckageFee,\n" +
                "  ISNULL(CA.OtehrInOutCost, 0) - ISNULL(OOI.OtherOutInCost, 0) OtehrInOutCost,\n" +
                "  ISNULL(CA.OtherInOutAmount, 0) - ISNULL(OOI.OtherOutInAmount, 0) OtherInOutAmount,\n" +
                "  ISNULL(CA.CarryForwordCost, 0) - ISNULL(CFC.CarryForwordCost, 0) CarryForwordCost,\n" +
                "  ISNULL(CA.CarryForwordTotal, 0) - ISNULL(CFC.CarryForwordAmount, 0) CarryForwordTotal,\n" +
                "  ISNULL(CA.PrintCostMargin, 0) PrintCostMargin,\n" +
                "  ISNULL(CA.StorageCostMargin, 0) StorageCostMargin,\n" +
                "  ISNULL(CA.SalesCostMargin, 0) SalesCostMargin,\n" +
                "  ISNULL(CA.AdjustCost, 0) - ISNULL(AMI.PrintCostMargin, 0) AdjustCost,\n" +
                "  ISNULL(CA.AdjustStorageCost, 0) - ISNULL(AMI.StorageCostMargin, 0) AdjustStorageCost,\n" +
                "  ISNULL(CA.AdjustFormCost, 0) - ISNULL(AMI.SalesCostMargin, 0) AdjustFormCost,\n" +
                "  ISNULL(CA.WaitCFTotal, 0) - ISNULL(PCI.CheckInAmount, 0) +\n" +
                "  ISNULL(CFC.CarryForwordAmount, 0) + ISNULL(OOI.OtherOutInAmount, 0) WaitCFTotal,\n" +
                "  (ISNULL(CA.CFPrintCost, 0) + ISNULL(CA.AdjustStorageCost, 0) -\n" +
                "   ISNULL(CA.OtehrInOutCost, 0) - ISNULL(CA.CarryForwordCost, 0) -\n" +
                "   ISNULL(PCI.CheckInCost, 0) - ISNULL(AMI.StorageCostMargin, 0) +\n" +
                "   ISNULL(CFC.CarryForwordCost, 0) + ISNULL(OOI.OtherOutInCost, 0)) AS WaitCarryForwordCost,\n" +
                "  ISNULL(B.BookPrice *\n" +
                "         (ISNULL(CA.WaitCFTotal, 0) - ISNULL(PCI.CheckInAmount, 0) +\n" +
                "          ISNULL(CFC.CarryForwordAmount, 0) +\n" +
                "          ISNULL(OOI.OtherOutInAmount, 0)),\n" +
                "         0) CCPListInCome,\n" +
                "  ISNULL(POF.BalancedFeeTotal, 0) BalancedFeeTotal,\n" +
                "  ISNULL(POF.UnBalancedFeeTotal, 0) UnBalancedFeeTotal,\n" +
                "  ISNULL(FEE. [ 稿费 / 校订费 / 审稿费 ], 0) RemunerationFees,\n" +
                "  ISNULL(FEE. [ 租型费 ], 0) HiredFees,\n" +
                "  ISNULL(FEE. [ 纸张费 ], 0) SheetFees,\n" +
                "  ISNULL(FEE. [ 装帧纸张费 ], 0) MaterialFees,\n" +
                "  ISNULL(FEE. [ 制版费 ], 0) MakeFees,\n" +
                "  ISNULL(FEE. [ 印刷费 ], 0) PrintingFees,\n" +
                "  ISNULL(FEE. [ 装订费 ], 0) BindFees,\n" +
                "  ISNULL(FEE. [ 其他直接费用 ], 0) PublishLossFees,\n" +
                "  ISNULL(FEE. [ 编录经费 ], 0) AnnalFees,\n" +
                "  ISNULL(FEE. [ 管理费 ], 0) ManageFees,\n" +
                "  ISNULL(FEE. [ 财务费 ], 0) FinanceFees,\n" +
                "  ISNULL(FEE. [ 销售费用 ], 0) SellFees\n" +
                "FROM (SELECT *,\n" +
                "        (CASE\n" +
                "         WHEN ParentsProjectId IS NOT NULL AND ParentsProjectId > 0 THEN\n" +
                "           ParentsProjectId\n" +
                "         ELSE\n" +
                "           ProjectId\n" +
                "         END) AS BProjectId\n" +
                "      FROM Project) AS P\n" +
                "  LEFT JOIN PrintersBalance pb\n" +
                "    ON p.ProjectId = pb.ProjectId\n" +
                "  LEFT JOIN (SELECT ca.ProjectId, CaudaBookAmount\n" +
                "             FROM CostAnalysis ca\n" +
                "               INNER JOIN PrintersBalance pb\n" +
                "                 ON ca.ProjectId = pb.ProjectId\n" +
                "             WHERE pb.StorageOverDate < @enddate\n" +
                "                   AND pb.StorageOverState = '44') cacba\n" +
                "    ON cacba.ProjectId = p.ProjectId\n" +
                "  LEFT OUTER JOIN Book AS B\n" +
                "    ON P.BookId = B.BookId\n" +
                "  LEFT OUTER JOIN ISBN\n" +
                "    ON P.ProjectId = ISBN.ProjectId\n" +
                "  LEFT OUTER JOIN EditorsBalance C\n" +
                "    ON P.ProjectId = C.ProjectId\n" +
                "  LEFT OUTER JOIN SendToPrinting SP\n" +
                "    ON P.ProjectId = SP.ProjectId\n" +
                "  LEFT OUTER JOIN (SELECT PROJECTID,\n" +
                "                     ISNULL(SUM(CHECKEDFEE), 0) AS CheckedFeeTotal,\n" +
                "                     ISNULL(SUM(BalancedFee), 0) AS BalancedFeeTotal,\n" +
                "                     ISNULL(SUM(UnBalancedFee), 0) AS UnBalancedFeeTotal\n" +
                "                   FROM ProjectOccuredFee\n" +
                "                   GROUP BY PROJECTID) POF\n" +
                "    ON P.ProjectId = POF.ProjectId\n" +
                "  LEFT OUTER JOIN CostAnalysis CA\n" +
                "    ON P.ProjectId = CA.ProjectId\n" +
                "  LEFT OUTER JOIN (SELECT ProjectId, ISNULL(SUM(CostSum), 0) CostSum\n" +
                "                   FROM (SELECT pcf.ProjectId,\n" +
                "                           ISNULL(CheckageThis, 0) - ISNULL(TaxSum, 0) CostSum\n" +
                "                         FROM ProjectCheckageFee pcf, Form F\n" +
                "                         WHERE F.AKTime >= @enddate\n" +
                "                               AND pcf.CheckageId = F.FormId\n" +
                "                         UNION ALL\n" +
                "                         SELECT ma.ProjectId, MaterialSum\n" +
                "                         FROM MaterialApply MA, FORM F\n" +
                "                         WHERE F.AKTime >= @enddate\n" +
                "                               AND MA.MaterialApplyId = F.FormId\n" +
                "                         UNION ALL\n" +
                "                         SELECT fsi.ProjectId, SeperateSum\n" +
                "                         FROM FeeSeperateItem FSI, FORM F\n" +
                "                         WHERE f.AKTime >= @enddate\n" +
                "                               AND FSI.SeperateId = F.FormId) C\n" +
                "                   GROUP BY ProjectId) cf\n" +
                "    ON P.ProjectId = cf.ProjectId\n" +
                "  LEFT OUTER JOIN (SELECT ProjectId,\n" +
                "                     SUM(CheckInAmount) CheckInAmount,\n" +
                "                     SUM(SellIncome) CheckInCost\n" +
                "                   FROM (SELECT PrintCheckInId,\n" +
                "                           ProjectId,\n" +
                "                           CheckInAmount,\n" +
                "                           SellIncome\n" +
                "                         FROM PrintCheckIn\n" +
                "                         WHERE ProjectId IS NOT NULL\n" +
                "                         UNION ALL\n" +
                "                         SELECT pci.PrintCheckInId,\n" +
                "                           Pci.ProjectId,\n" +
                "                           Pci.CheckInAmount,\n" +
                "                           Pci.SellIncome\n" +
                "                         FROM PrintCheckIn pci, PrintForm pf\n" +
                "                         WHERE pci.PrintFormId = pf.PrintFormId\n" +
                "                               AND pci.ProjectId IS NULL\n" +
                "                         UNION ALL\n" +
                "                         SELECT pci.PrintCheckInId,\n" +
                "                           cf.ProjectId,\n" +
                "                           Pci.CheckInAmount,\n" +
                "                           Pci.SellIncome\n" +
                "                         FROM PrintCheckIn pci, ColligateForm cf\n" +
                "                         WHERE pci.PrintFormId = cf.ColligateFormId\n" +
                "                               AND pci.ProjectId IS NULL) pci,\n" +
                "                     Form F\n" +
                "                   WHERE pci.PrintCheckInId = F.FormId\n" +
                "                         AND F.AKTime >= @enddate\n" +
                "                   GROUP BY ProjectId) pci\n" +
                "    ON P.ProjectId = pci.ProjectId\n" +
                "  LEFT OUTER JOIN (SELECT ProjectId,\n" +
                "                     SUM(PrintCostMargin) PrintCostMargin,\n" +
                "                     SUM(StorageCostMargin) StorageCostMargin,\n" +
                "                     SUM(SalesCostMargin) SalesCostMargin\n" +
                "                   FROM AdjustMarginItem AMI, FORM F\n" +
                "                   WHERE F.AKTime >= @enddate\n" +
                "                         AND AMI.MarginId = F.FORMID\n" +
                "                   GROUP BY ProjectId) ami\n" +
                "    ON P.ProjectId = ami.ProjectId\n" +
                "  LEFT OUTER JOIN (SELECT ProjectId,\n" +
                "                     SUM(OtherOutInAmount) OtherOutInAmount,\n" +
                "                     SUM(OtherOutInCost) OtherOutInCost\n" +
                "                   FROM (SELECT A.ProjectId,\n" +
                "                           SUM(Fi.BookAmount) OtherOutInAmount,\n" +
                "                           SUM(Fi.SellIncome) OtherOutInCost\n" +
                "                         FROM form                  F,\n" +
                "                           FormItem              Fi,\n" +
                "                           AssistantCarryForward A\n" +
                "                         WHERE F.FormId = FI.FormId\n" +
                "                               AND F.FormId = A.FormId\n" +
                "                               AND F.FormType NOT IN ('36', '37')\n" +
                "                               AND F.AKTime >= @enddate\n" +
                "                               AND FI.BookId = A.BookId\n" +
                "                         GROUP BY A.ProjectId\n" +
                "                         UNION ALL\n" +
                "                         SELECT A.ProjectId,\n" +
                "                           -SUM(Fi.BookAmount) OtherInAmount,\n" +
                "                           -SUM(Fi.SellIncome) OtherInCost\n" +
                "                         FROM form                  F,\n" +
                "                           FormItem              Fi,\n" +
                "                           AssistantCarryForward A\n" +
                "                         WHERE F.FormId = FI.FormId\n" +
                "                               AND F.FormId = A.FormId\n" +
                "                               AND F.FormType IN ('36', '37')\n" +
                "                               AND F.AKTime >= @enddate\n" +
                "                               AND FI.BookId = A.BookId\n" +
                "                         GROUP BY A.ProjectId\n" +
                "                         UNION ALL\n" +
                "                         SELECT rfi.OutProjectId AS ProjectId,\n" +
                "                                SUM(rfi.OutAmount) OtherInAmount,\n" +
                "                                SUM(rfi.OutWaitCarryForwordCost) OtherInCost\n" +
                "                         FROM form F, RefitItem rfi\n" +
                "                         WHERE F.FormId = rfi.RefitId\n" +
                "                               AND F.AKTime >= @enddate\n" +
                "                         GROUP BY rfi.OutProjectId) t\n" +
                "                   GROUP BY ProjectId) ooi\n" +
                "    ON P.ProjectId = ooi.ProjectId\n" +
                "  LEFT OUTER JOIN (SELECT ProjectId,\n" +
                "                     SUM(CarryForwordAmount) CarryForwordAmount,\n" +
                "                     SUM(CarryForwordCost) CarryForwordCost\n" +
                "                   FROM (SELECT ProjectId,\n" +
                "                           SUM(ISNULL(CFAmount, 0)) CarryForwordAmount,\n" +
                "                           SUM(ISNULL(CFCost, 0)) CarryForwordCost\n" +
                "                         FROM CarryForwardProject CFP, Form F\n" +
                "                         WHERE F.AKTime >= @enddate\n" +
                "                               AND CFP.CarryForwardId = F.FormId\n" +
                "                         GROUP BY ProjectId\n" +
                "                         UNION ALL\n" +
                "                         SELECT ProjectId,\n" +
                "                           SUM(ISNULL(CarryForwardAmount, 0)) CarryForwordAmount,\n" +
                "                           SUM(ISNULL(CarryForwardCost, 0)) CarryForwordCost\n" +
                "                         FROM CarryForwardStart cfs, Form F\n" +
                "                         WHERE F.AKTime >= @enddate\n" +
                "                               AND cfs.CarryForwardId = F.FormId\n" +
                "                         GROUP BY ProjectId) C\n" +
                "                   GROUP BY ProjectId) cfc\n" +
                "    ON P.ProjectId = cfc.ProjectId\n" +
                "  LEFT OUTER JOIN (SELECT POF.ProjectId,\n" +
                "                     SUM(CASE CG.DisplayName\n" +
                "                         WHEN '稿费/校订费/审稿费' THEN\n" +
                "                           ROUND(ISNULL(CheckedFee, 0), 2)\n" +
                "                         ELSE\n" +
                "                           0.00\n" +
                "                         END) [ 稿费 / 校订费 / 审稿费 ],\n" +
                "                     SUM(CASE CG.DisplayName\n" +
                "                         WHEN '租型费' THEN\n" +
                "                           ROUND(ISNULL(CheckedFee, 0), 2)\n" +
                "                         ELSE\n" +
                "                           0.00\n" +
                "                         END) [ 租型费 ],\n" +
                "                     SUM(CASE CG.DisplayName\n" +
                "                         WHEN '纸张费' THEN\n" +
                "                           ROUND(ISNULL(CheckedFee, 0), 2)\n" +
                "                         ELSE\n" +
                "                           0.00\n" +
                "                         END) [ 纸张费 ],\n" +
                "                     SUM(CASE CG.DisplayName\n" +
                "                         WHEN '装帧纸张费' THEN\n" +
                "                           ROUND(ISNULL(CheckedFee, 0), 2)\n" +
                "                         ELSE\n" +
                "                           0.00\n" +
                "                         END) [ 装帧纸张费 ],\n" +
                "                     SUM(CASE CG.DisplayName\n" +
                "                         WHEN '制版费' THEN\n" +
                "                           ROUND(ISNULL(CheckedFee, 0), 2)\n" +
                "                         ELSE\n" +
                "                           0.00\n" +
                "                         END) [ 制版费 ],\n" +
                "                     SUM(CASE CG.DisplayName\n" +
                "                         WHEN '印刷费' THEN\n" +
                "                           ROUND(ISNULL(CheckedFee, 0), 2)\n" +
                "                         ELSE\n" +
                "                           0.00\n" +
                "                         END) [ 印刷费 ],\n" +
                "                     SUM(CASE CG.DisplayName\n" +
                "                         WHEN '装订费' THEN\n" +
                "                           ROUND(ISNULL(CheckedFee, 0), 2)\n" +
                "                         ELSE\n" +
                "                           0.00\n" +
                "                         END) [ 装订费 ],\n" +
                "                     SUM(CASE CG.DisplayName\n" +
                "                         WHEN '其他直接费用' THEN\n" +
                "                           ROUND(ISNULL(CheckedFee, 0), 2)\n" +
                "                         ELSE\n" +
                "                           0.00\n" +
                "                         END) [ 其他直接费用 ],\n" +
                "                     SUM(CASE CG.DisplayName\n" +
                "                         WHEN '编录经费' THEN\n" +
                "                           ROUND(ISNULL(CheckedFee, 0), 2)\n" +
                "                         ELSE\n" +
                "                           0.00\n" +
                "                         END) [ 编录经费 ],\n" +
                "                     SUM(CASE CG.DisplayName\n" +
                "                         WHEN '管理费' THEN\n" +
                "                           ROUND(ISNULL(CheckedFee, 0), 2)\n" +
                "                         ELSE\n" +
                "                           0.00\n" +
                "                         END) [ 管理费 ],\n" +
                "                     SUM(CASE CG.DisplayName\n" +
                "                         WHEN '财务费' THEN\n" +
                "                           ROUND(ISNULL(CheckedFee, 0), 2)\n" +
                "                         ELSE\n" +
                "                           0.00\n" +
                "                         END) [ 财务费 ],\n" +
                "                     SUM(CASE CG.DisplayName\n" +
                "                         WHEN '销售费用' THEN\n" +
                "                           ROUND(ISNULL(CheckedFee, 0), 2)\n" +
                "                         ELSE\n" +
                "                           0.00\n" +
                "                         END) [ 销售费用 ]\n" +
                "                   FROM ProjectOccuredFee POF,\n" +
                "                     (SELECT BalanceItemId, Costproperty\n" +
                "                      FROM balancetypeanditems) T,\n" +
                "                     (SELECT StoredValue,\n" +
                "                        cast(DisplayName AS VARCHAR(24)) DisplayName\n" +
                "                      FROM CONSTGROUPSET\n" +
                "                      WHERE GROUPID = 170) AS CG\n" +
                "                   WHERE T.Costproperty = CG.StoredValue\n" +
                "                         AND POF.BalanceItemId = T.BalanceItemId\n" +
                "                   GROUP BY POF.ProjectId) AS FEE\n" +
                "    ON P.ProjectId = FEE.ProjectId\n" +
                "WHERE ((ISNULL(CA.WaitCFTotal, 0) - ISNULL(PCI.CheckInAmount, 0) +\n" +
                "        ISNULL(CFC.CarryForwordAmount, 0) +\n" +
                "        ISNULL(OOI.OtherOutInAmount, 0)) <> 0 OR\n" +
                "       (ISNULL(CA.CFPrintCost, 0) + ISNULL(CA.AdjustStorageCost, 0) -\n" +
                "        ISNULL(CA.OtehrInOutCost, 0) - ISNULL(CA.CarryForwordCost, 0) -\n" +
                "        ISNULL(PCI.CheckInCost, 0) - ISNULL(AMI.StorageCostMargin, 0) +\n" +
                "        ISNULL(CFC.CarryForwordCost, 0) + ISNULL(OOI.OtherOutInCost, 0)) <> 0)\n" +
                "      AND P.ProjectId IN (\n" +
                "  SELECT ProjectId FROM Project WHERE ProjectProperty<>7\n" +
                "  UNION\n" +
                "  SELECT pt.ProjectId FROM Project pt INNER JOIN PrintersBalance pb ON pt.ProjectId = pb.ProjectId\n" +
                "  WHERE pt.ProjectProperty= 7 AND pb.MerchandiseFirstState= 0x2c\n" +
                ")\n" +
                "      AND P.ProjectState NOT IN (5,7,8)\n";
        String strDel = "DELETE FROM BASE_PUB_BOOK WHERE UNITCODE = ?";
        Long endTime;
        Long StartTime;
        try {
            StartTime = System.currentTimeMillis();
            connPUBERP = DBUtils.getConnection(PhName);
            pstmtDataSrc = connPUBERP.prepareStatement(strSqlDataSrc);
            pstmtDataSrc.setString(1,"2018-01-01");
            rsDataSrc = pstmtDataSrc.executeQuery();

            connBI = DBUtils.getConnection("SPDGBI");
            connBI.setAutoCommit(false);
            lstTblInfo = Udfs.GetTableInfo("BASE_PUB_INVENTORY_DETAIL");

            pstmtDp = connBI.prepareStatement(strDel);
            pstmtDp.setString(1,PhName);

            pstmtDp.execute();

            pstmtDp = connBI.prepareStatement(((Map)lstTblInfo.get(lstTblInfo.size() - 1)).get("sqlins").toString());

            oracle.sql.CLOB clob = null;
            while (rsDataSrc.next()) {
                pstmtDp.setString(1, PhName);
                pstmtDp.setString(2,"2017");
                pstmtDp.setString(3,"12");
                for (int i = 3; i < lstTblInfo.size() - 1; i++) {
                    switch (lstDataType.indexOf(((Map)lstTblInfo.get(i)).get("DATA_TYPE").toString())) {
                        case 0:
                            pstmtDp.setFloat(i + 1, rsDataSrc.getFloat(((Map)lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                        case 1:
                        case 2:
                        case 3:
                        case 4:
                            pstmtDp.setString(i + 1, rsDataSrc.getString(((Map)lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                        case 5:
                            clob = createTemporaryCLOB(connBI, false, oracle.sql.CLOB.DURATION_SESSION);
                            clob.open(oracle.sql.CLOB.MODE_READWRITE);
                            clob.setString(1, rsDataSrc.getString(lstTblInfo.get(i).toString()));
                            pstmtDp.setClob(i + 1, clob);
                            break;
                        default:
                            pstmtDp.setObject(i + 1, rsDataSrc.getString(((Map)lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                    }
                }
                pstmtDp.addBatch();
            }
            pstmtDp.executeBatch();
            pstmtDp.clearParameters();
            pstmtDp.clearBatch();
            connBI.commit();
            endTime = System.currentTimeMillis();
            System.out.println("抽取数据完成，出版单位编码为" + PhName + "的书卡数据抽取完成，用时：" + (endTime - StartTime));

        } catch (Exception ex) {
            ex.printStackTrace();
        }finally {
            lstTblInfo = null;
            DBUtils.close(connBI);
        }

    }
}
